{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Planet Data Collection\n",
    "Using the Open Exoplanet Catalogue database: https://github.com/OpenExoplanetCatalogue/open_exoplanet_catalogue/\n",
    "\n",
    "## Data License\n",
    "Copyright (C) 2012 Hanno Rein\n",
    "\n",
    "Permission is hereby granted, free of charge, to any person obtaining a copy of this database and associated scripts (the \"Database\"), to deal in the Database without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Database, and to permit persons to whom the Database is furnished to do so, subject to the following conditions:\n",
    "\n",
    "The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Database. A reference to the Database shall be included in all scientific publications that make use of the Database.\n",
    "\n",
    "THE DATABASE IS PROVIDED \"AS IS\", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE DATABASE OR THE USE OR OTHER DEALINGS IN THE DATABASE."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Follow instructions to get the xml file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import xml.etree.ElementTree as ET, urllib.request, gzip, io\n",
    "url = \"https://github.com/OpenExoplanetCatalogue/oec_gzip/raw/master/systems.xml.gz\"\n",
    "oec = ET.parse(gzip.GzipFile(fileobj=io.BytesIO(urllib.request.urlopen(url).read())))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Parse into Pandas DataFrame\n",
    "Information on what each field means can be found [here](https://github.com/OpenExoplanetCatalogue/open_exoplanet_catalogue/#data-structure)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "def parse(base):\n",
    "    db = oec.findall(f\".//{base}\")\n",
    "    \n",
    "    exclude = ['star', 'videolink', 'binary'] if base in ['system', 'binary'] else ['planet']\n",
    "    \n",
    "    columns = set([attribute.tag for attribute in db[0].getchildren() if attribute.tag not in exclude])\n",
    "    results = pd.DataFrame(columns=columns)\n",
    "\n",
    "    for entry in db:\n",
    "        data = {col : entry.findtext(col) for col in columns}\n",
    "        if base in ['system', 'binary']:\n",
    "            data['binaries'] = len(entry.findall('.//binary'))\n",
    "            data['stars'] = len(entry.findall('.//star'))\n",
    "        if base in ['system', 'star', 'binary']:\n",
    "            data['planets'] = len(entry.findall('.//planet'))\n",
    "        results = results.append(data, ignore_index=True)\n",
    "\n",
    "    return results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Parse planet data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>period</th>\n",
       "      <th>name</th>\n",
       "      <th>eccentricity</th>\n",
       "      <th>description</th>\n",
       "      <th>discoverymethod</th>\n",
       "      <th>periastrontime</th>\n",
       "      <th>lastupdate</th>\n",
       "      <th>semimajoraxis</th>\n",
       "      <th>mass</th>\n",
       "      <th>periastron</th>\n",
       "      <th>list</th>\n",
       "      <th>discoveryyear</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>326.03</td>\n",
       "      <td>11 Com b</td>\n",
       "      <td>0.231</td>\n",
       "      <td>11 Com b is a brown dwarf-mass companion to th...</td>\n",
       "      <td>RV</td>\n",
       "      <td>2452899.6</td>\n",
       "      <td>15/09/20</td>\n",
       "      <td>1.29</td>\n",
       "      <td>19.4</td>\n",
       "      <td>94.8</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>516.22</td>\n",
       "      <td>11 UMi b</td>\n",
       "      <td>0.08</td>\n",
       "      <td>11 Ursae Minoris is a star located in the cons...</td>\n",
       "      <td>RV</td>\n",
       "      <td>2452861.04</td>\n",
       "      <td>15/09/20</td>\n",
       "      <td>1.54</td>\n",
       "      <td>11.20</td>\n",
       "      <td>117.63</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>185.84</td>\n",
       "      <td>14 And b</td>\n",
       "      <td>0</td>\n",
       "      <td>14 Andromedae is an evolved star in the conste...</td>\n",
       "      <td>RV</td>\n",
       "      <td>2452861.4</td>\n",
       "      <td>15/09/20</td>\n",
       "      <td>0.83</td>\n",
       "      <td>4.8</td>\n",
       "      <td>0</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1766</td>\n",
       "      <td>14 Her b</td>\n",
       "      <td>0.359</td>\n",
       "      <td>The star 14 Herculis is only 59 light years aw...</td>\n",
       "      <td>RV</td>\n",
       "      <td>None</td>\n",
       "      <td>15/09/21</td>\n",
       "      <td>2.864</td>\n",
       "      <td>4.975</td>\n",
       "      <td>22.230</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td>2002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9886</td>\n",
       "      <td>14 Her c</td>\n",
       "      <td>0.184</td>\n",
       "      <td>14 Her c is the second companion in the system...</td>\n",
       "      <td>RV</td>\n",
       "      <td>None</td>\n",
       "      <td>15/09/21</td>\n",
       "      <td>9.037</td>\n",
       "      <td>7.679</td>\n",
       "      <td>189.076</td>\n",
       "      <td>Controversial</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   period      name eccentricity  \\\n",
       "0  326.03  11 Com b        0.231   \n",
       "1  516.22  11 UMi b         0.08   \n",
       "2  185.84  14 And b            0   \n",
       "3    1766  14 Her b        0.359   \n",
       "4    9886  14 Her c        0.184   \n",
       "\n",
       "                                         description discoverymethod  \\\n",
       "0  11 Com b is a brown dwarf-mass companion to th...              RV   \n",
       "1  11 Ursae Minoris is a star located in the cons...              RV   \n",
       "2  14 Andromedae is an evolved star in the conste...              RV   \n",
       "3  The star 14 Herculis is only 59 light years aw...              RV   \n",
       "4  14 Her c is the second companion in the system...              RV   \n",
       "\n",
       "  periastrontime lastupdate semimajoraxis   mass periastron  \\\n",
       "0      2452899.6   15/09/20          1.29   19.4       94.8   \n",
       "1     2452861.04   15/09/20          1.54  11.20     117.63   \n",
       "2      2452861.4   15/09/20          0.83    4.8          0   \n",
       "3           None   15/09/21         2.864  4.975     22.230   \n",
       "4           None   15/09/21         9.037  7.679    189.076   \n",
       "\n",
       "                list discoveryyear  \n",
       "0  Confirmed planets          2008  \n",
       "1  Confirmed planets          2009  \n",
       "2  Confirmed planets          2008  \n",
       "3  Confirmed planets          2002  \n",
       "4      Controversial          2006  "
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "planets = parse('planet')\n",
    "planets.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Parse system data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "systems = parse('system')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rightascension</th>\n",
       "      <th>name</th>\n",
       "      <th>distance</th>\n",
       "      <th>declination</th>\n",
       "      <th>binaries</th>\n",
       "      <th>planets</th>\n",
       "      <th>stars</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12 20 43</td>\n",
       "      <td>11 Com</td>\n",
       "      <td>88.9</td>\n",
       "      <td>+17 47 34</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15 17 05.88899</td>\n",
       "      <td>11 UMi</td>\n",
       "      <td>122.1</td>\n",
       "      <td>+71 49 26.0466</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>23 31 17.41346</td>\n",
       "      <td>14 And</td>\n",
       "      <td>79.2</td>\n",
       "      <td>+39 14 10.3092</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16 10 23</td>\n",
       "      <td>14 Her</td>\n",
       "      <td>18.1</td>\n",
       "      <td>+43 49 18</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>19 41 48.95343</td>\n",
       "      <td>16 Cygni</td>\n",
       "      <td>21.146</td>\n",
       "      <td>+50 31 30.2153</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   rightascension      name distance     declination  binaries  planets  stars\n",
       "0        12 20 43    11 Com     88.9       +17 47 34       0.0      1.0    1.0\n",
       "1  15 17 05.88899    11 UMi    122.1  +71 49 26.0466       0.0      1.0    1.0\n",
       "2  23 31 17.41346    14 And     79.2  +39 14 10.3092       0.0      1.0    1.0\n",
       "3        16 10 23    14 Her     18.1       +43 49 18       0.0      2.0    1.0\n",
       "4  19 41 48.95343  16 Cygni   21.146  +50 31 30.2153       2.0      1.0    3.0"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "systems.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Parse binary data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>separation</th>\n",
       "      <th>name</th>\n",
       "      <th>positionangle</th>\n",
       "      <th>binaries</th>\n",
       "      <th>planets</th>\n",
       "      <th>stars</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>39.56</td>\n",
       "      <td>16 Cygni</td>\n",
       "      <td>133.30</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.4</td>\n",
       "      <td>16 Cygni AC</td>\n",
       "      <td>209</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12.37</td>\n",
       "      <td>2M0441+2301</td>\n",
       "      <td>237.3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.2323</td>\n",
       "      <td>2M 044145</td>\n",
       "      <td>79.61</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>None</td>\n",
       "      <td>2M 1938+4603</td>\n",
       "      <td>None</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  separation          name positionangle  binaries  planets  stars\n",
       "0      39.56      16 Cygni        133.30       1.0      1.0    3.0\n",
       "1        3.4   16 Cygni AC           209       0.0      0.0    2.0\n",
       "2      12.37   2M0441+2301         237.3       1.0      1.0    3.0\n",
       "3     0.2323     2M 044145         79.61       0.0      0.0    2.0\n",
       "4       None  2M 1938+4603          None       0.0      1.0    2.0"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "binaries = parse('binary')\n",
    "binaries.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Parse star data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>magJ</th>\n",
       "      <th>name</th>\n",
       "      <th>radius</th>\n",
       "      <th>spectraltype</th>\n",
       "      <th>magB</th>\n",
       "      <th>magV</th>\n",
       "      <th>temperature</th>\n",
       "      <th>magK</th>\n",
       "      <th>mass</th>\n",
       "      <th>metallicity</th>\n",
       "      <th>magH</th>\n",
       "      <th>planets</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2.943</td>\n",
       "      <td>11 Com</td>\n",
       "      <td>19</td>\n",
       "      <td>G8 III</td>\n",
       "      <td>5.74</td>\n",
       "      <td>4.74</td>\n",
       "      <td>4742</td>\n",
       "      <td>2.282</td>\n",
       "      <td>2.7</td>\n",
       "      <td>-0.35</td>\n",
       "      <td>2.484</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.876</td>\n",
       "      <td>11 UMi</td>\n",
       "      <td>24.08</td>\n",
       "      <td>K4III</td>\n",
       "      <td>6.415</td>\n",
       "      <td>5.024</td>\n",
       "      <td>4340</td>\n",
       "      <td>1.939</td>\n",
       "      <td>1.80</td>\n",
       "      <td>0.04</td>\n",
       "      <td>2.091</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.019</td>\n",
       "      <td>14 And</td>\n",
       "      <td>11</td>\n",
       "      <td>K0III</td>\n",
       "      <td>6.24</td>\n",
       "      <td>5.22</td>\n",
       "      <td>4813</td>\n",
       "      <td>2.331</td>\n",
       "      <td>2.2</td>\n",
       "      <td>-0.24</td>\n",
       "      <td>2.608</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5.158</td>\n",
       "      <td>14 Her</td>\n",
       "      <td>0.708</td>\n",
       "      <td>K0 V</td>\n",
       "      <td>7.57</td>\n",
       "      <td>6.67</td>\n",
       "      <td>5311.0</td>\n",
       "      <td>4.714</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.43</td>\n",
       "      <td>4.803</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.09</td>\n",
       "      <td>16 Cygni A</td>\n",
       "      <td>1.243</td>\n",
       "      <td>G2V</td>\n",
       "      <td>6.59</td>\n",
       "      <td>5.95</td>\n",
       "      <td>5825</td>\n",
       "      <td>4.43</td>\n",
       "      <td>1.11</td>\n",
       "      <td>0.096</td>\n",
       "      <td>4.72</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    magJ        name radius spectraltype   magB   magV temperature   magK  \\\n",
       "0  2.943      11 Com     19       G8 III   5.74   4.74        4742  2.282   \n",
       "1  2.876      11 UMi  24.08        K4III  6.415  5.024        4340  1.939   \n",
       "2  3.019      14 And     11        K0III   6.24   5.22        4813  2.331   \n",
       "3  5.158      14 Her  0.708         K0 V   7.57   6.67      5311.0  4.714   \n",
       "4   5.09  16 Cygni A  1.243          G2V   6.59   5.95        5825   4.43   \n",
       "\n",
       "   mass metallicity   magH  planets  \n",
       "0   2.7       -0.35  2.484      1.0  \n",
       "1  1.80        0.04  2.091      1.0  \n",
       "2   2.2       -0.24  2.608      1.0  \n",
       "3   1.0        0.43  4.803      2.0  \n",
       "4  1.11       0.096   4.72      0.0  "
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stars = parse('star')\n",
    "stars.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Save to CSVs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "planets.to_csv('data/planets.csv', index=False)\n",
    "binaries.to_csv('data/binaries.csv', index=False)\n",
    "stars.to_csv('data/stars.csv', index=False)\n",
    "systems.to_csv('data/systems.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
